﻿Imports System.Data.OleDb
Public Class RecipeDetails
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'only do this if arrived from a different page
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("RecipeList.aspx")
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
            'select all fields from Recipe Comments table where fkID from Query string is correct recipe
            Dim SqlString As String = "SELECT * FROM RecipeCommentsView WHERE [Recipe_fkID]=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            'declare f1 variable to provide query string ID
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            'as data will be returned, use ExecuteReader not ExecuteNonQuery
            oleDbConn.Open()
            Dim dataReader = cmd.ExecuteReader()
            GridView2.DataSource = dataReader
            GridView2.DataBind()

            'log when a user views a recipe
            Dim RecipesViewedSql As String = "INSERT INTO RecipesViewed(Viewer, Recipe_fkID) VALUES (@f1,@f2)"
            Dim cmd2 As OleDbCommand = New OleDbCommand(RecipesViewedSql, oleDbConn)
            cmd2.CommandType = CommandType.Text
            cmd2.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmd2.Parameters.AddWithValue("@f2", Request.Params("ID"))
            cmd2.ExecuteNonQuery()




        End If
    End Sub

    Protected Sub btn_submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_submit.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
        Dim SqlString As String = "Insert into RecipeComments([UserName],[Comment_title],[Comment],[Rating],[Recipe_fkID]) Values (@f1,@f2,@f3,@f4,@f5)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_CommentTitle.Text)
        cmd.Parameters.AddWithValue("@f3", tb_Comment.Text)
        cmd.Parameters.AddWithValue("@f4", rbl_Rating.SelectedValue)
        cmd.Parameters.AddWithValue("@f5", Request.Params("ID"))
        oleDbConn.Open()
        cmd.ExecuteNonQuery()
        'if isPostBack is true, fill GridView2 with recipe comments
        Dim fillComments As String = "SELECT * FROM RecipeCommentsView WHERE [Recipe_fkID]=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dataReader = fillCommentsCmd.ExecuteReader()
        GridView2.DataSource = dataReader
        GridView2.DataBind()

        'reset fields to blank
        tb_Comment.Text = ""
        tb_CommentTitle.Text = ""
        rbl_Rating.SelectedValue = ""



    End Sub

    Protected Function getRating(ByVal rating As Integer)
        Dim retval As String
        retval = ""
        For i = 1 To rating
            retval = retval + "*"
        Next
        Return retval
    End Function

    Protected Sub btn_Tag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Tag.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
        Dim SqlString As String = "INSERT INTO RecipeTagJunction(Recipe_fkID,Tag_fkID) VALUES (@f1,@f2) "
        oleDbConn.Open()
        'loop through to see which tag or tags are selected
        For Each cbl As ListItem In cbl_Tags.Items
            If cbl.Selected Then
                'add join to the tags and recipe table
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmd.Parameters.AddWithValue("@f2", cbl.Value)
                cmd.ExecuteNonQuery()
                'once value added to db, tick is removed from page.
                cbl.Selected = False
            End If
        Next

        'If list of new tags is empty then ignore the rest of the function.
        If tb_tags.Text = "" Then Return

        'split new tags textbox and break into an array
        Dim newTags = tb_tags.Text.Split(",")
        ' newTags is temporary array, newTag is temporary variable
        For Each newTag In newTags
            'add to the table of recipe tags
            Dim newTagSql As String = "INSERT into TagsRecipes(Tag) VALUES(@f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            'find the id of the new tag in the table
            'select the top result of the table when the ID is sorted descending (i.e. the highest number will be at the top)
            Dim getLastIDComd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID from TagsRecipes ORDER BY ID DESC", oleDbConn)
            Dim dataReader2 = getLastIDComd.ExecuteReader()
            dataReader2.Read()
            Dim lastID = dataReader2.GetValue(0)

            'link the new tag ID with the restaurantID in the junction table
            Dim cmd1 As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd1.CommandType = CommandType.Text
            cmd1.Parameters.AddWithValue("@f1", Request.Params("ID"))
            cmd1.Parameters.AddWithValue("@f2", lastID)
            cmd1.ExecuteNonQuery()

            'clear the Textbox with new tags in it
            tb_tags.Text = ""
        Next
    End Sub

 
End Class